package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Distrib;
import com.xxxy.jsq.model.Page;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
 
public class DistribDao implements BaseDao<Distrib> {
    private C3P0Utils db = new C3P0Utils();

    @Override
    public String add(Distrib model) {
        Connection conn = db.getConnection();
        String sql = "INSERT INTO pa_distrib(role_id, works_id, distrib_memo, distrib_flag) VALUES(?,?,?,?)";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setLong(1, model.getRoleId());
            pstm.setLong(2, model.getWorksId());
            pstm.setString(3, model.getDistribMemo());
            pstm.setString(4, model.getDistribFlag());

            return pstm.executeUpdate() > 0 ? "Ok" : "Err";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
    }

    @Override
    public String update(Distrib model) {
        Connection conn = db.getConnection();
        String sql = "UPDATE pa_distrib SET role_id=?, works_id=?, distrib_memo=?, distrib_flag=? WHERE distrib_id=?";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setLong(1, model.getRoleId());
            pstm.setLong(2, model.getWorksId());
            pstm.setString(3, model.getDistribMemo());
            pstm.setString(4, model.getDistribFlag());
            pstm.setLong(5, model.getDistribId());

            return pstm.executeUpdate() > 0 ? "Ok" : "Err";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
    }

    @Override
    public String delete(Distrib model) {
        return deleteById(model.getDistribId());
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = db.getConnection();
        String sql = "DELETE FROM pa_distrib WHERE distrib_id=?";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setLong(1, cid);
            return pstm.executeUpdate() > 0 ? "Ok" : "Err";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
    }

    @Override
    public List<Distrib> getAllData(Distrib model) {
        List<Distrib> list = new ArrayList<>();
        Connection conn = db.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM pa_distrib WHERE 1=1 ");
        
        if(model.getRoleId() != null) {
            sql.append("AND role_id=").append(model.getRoleId()).append(" ");
        }
        if(model.getWorksId() != null) {
            sql.append("AND works_id=").append(model.getWorksId()).append(" ");
        }
        if(model.getDistribFlag() != null && !model.getDistribFlag().isEmpty()) {
            sql.append("AND distrib_flag='").append(model.getDistribFlag()).append("' ");
        }
        sql.append("ORDER BY distrib_stmp DESC");
        
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql.toString())) {
            while(rs.next()) {
                Distrib distrib = new Distrib();
                distrib.setDistribId(rs.getLong("distrib_id"));
                distrib.setRoleId(rs.getLong("role_id"));
                distrib.setWorksId(rs.getLong("works_id"));
                distrib.setDistribDate(rs.getTimestamp("distrib_date"));
                distrib.setDistribMemo(rs.getString("distrib_memo"));
                distrib.setDistribFlag(rs.getString("distrib_flag"));
                list.add(distrib);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Distrib findById(Long cid) {
        Connection conn = db.getConnection();
        String sql = "SELECT * FROM pa_distrib WHERE distrib_id=?";
        
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setLong(1, cid);
            ResultSet rs = pstm.executeQuery();
            if(rs.next()) {
                Distrib distrib = new Distrib();
                distrib.setDistribId(rs.getLong("distrib_id"));
                distrib.setRoleId(rs.getLong("role_id"));
                distrib.setWorksId(rs.getLong("works_id"));
                distrib.setDistribDate(rs.getTimestamp("distrib_date"));
                distrib.setDistribMemo(rs.getString("distrib_memo"));
                distrib.setDistribFlag(rs.getString("distrib_flag"));
                return distrib;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Distrib find(Distrib model) {
        return findById(model.getDistribId());
    }

    @Override
    public boolean isSame(Distrib model, boolean sflag) {
        return false;
    }

    @Override
    public List<Distrib> getAllDataByPage(Distrib model, Page page) {
        List<Distrib> list = new ArrayList<>();
        Connection conn = db.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM pa_distrib WHERE 1=1 ");
        
        if(model.getRoleId() != null) {
            sql.append("AND role_id=").append(model.getRoleId()).append(" ");
        }
        if(model.getWorksId() != null) {
            sql.append("AND works_id=").append(model.getWorksId()).append(" ");
        }
        if(model.getDistribFlag() != null && !model.getDistribFlag().isEmpty()) {
            sql.append("AND distrib_flag='").append(model.getDistribFlag()).append("' ");
        }
        sql.append("ORDER BY distrib_stmp DESC LIMIT ?,?");
        
        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            pstm.setInt(1, (page.getCurrentPage()-1)*page.getCount());
            pstm.setInt(2, page.getCount());
            ResultSet rs = pstm.executeQuery();
            while(rs.next()) {
                Distrib distrib = new Distrib();
                distrib.setDistribId(rs.getLong("distrib_id"));
                distrib.setRoleId(rs.getLong("role_id"));
                distrib.setWorksId(rs.getLong("works_id"));
                distrib.setDistribDate(rs.getTimestamp("distrib_date"));
                distrib.setDistribMemo(rs.getString("distrib_memo"));
                distrib.setDistribFlag(rs.getString("distrib_flag"));
                list.add(distrib);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Distrib model) {
        Connection conn = db.getConnection();
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM pa_distrib WHERE 1=1 ");
        
        if(model.getRoleId() != null) {
            sql.append("AND role_id=").append(model.getRoleId()).append(" ");
        }
        if(model.getWorksId() != null) {
            sql.append("AND works_id=").append(model.getWorksId()).append(" ");
        }
        if(model.getDistribFlag() != null && !model.getDistribFlag().isEmpty()) {
            sql.append("AND distrib_flag='").append(model.getDistribFlag()).append("' ");
        }
        
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql.toString())) {
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Distrib> getAllky() {
        return getAllData(new Distrib());
    }
}